package services

import (
	"database/sql"
	"fmt"
	"web2/pkg/models"
)

// 分页查询
func GetProducts(db *sql.DB, page, pageSize int) ([]models.Product, error) {
	offset := (page - 1) * pageSize

	rows, err := db.Query(`
        SELECT id, name, model, brand, category_id, 
               purchase_price, retail_price
        FROM products
        ORDER BY id DESC
        LIMIT ? OFFSET ?
    `, pageSize, offset)

	if err != nil {
		return nil, fmt.Errorf("查询产品列表失败: %v", err)
	}
	defer rows.Close()

	var products []models.Product
	for rows.Next() {
		var p models.Product
		if err := rows.Scan(
			&p.ID, &p.Name, &p.Model, &p.Brand, &p.CategoryID,
			&p.PurchasePrice, &p.RetailPrice); err != nil {
			return nil, fmt.Errorf("扫描产品数据失败: %v", err)
		}
		products = append(products, p)
	}

	return products, nil
}

func CreateProduct(db *sql.DB, product *models.Product) (int64, error) {

	result, err := db.Exec(`
        INSERT INTO products (
            name, model, brand, category_id, specs, unit,
            purchase_price, retail_price, min_stock, max_stock, description
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `,
		product.Name, product.Model, product.Brand, product.CategoryID,
		product.Specs, product.Unit, product.PurchasePrice, product.RetailPrice,
		product.MinStock, product.MaxStock, product.Description)

	if err != nil {
		return 0, fmt.Errorf("创建产品失败: %v", err)
	}

	return result.LastInsertId()
}
func GetProduct(db *sql.DB, id int) (*models.Product, error) {
	var product models.Product
	err := db.QueryRow(`
        SELECT id, name, model, brand, category_id, specs, unit,
               purchase_price, retail_price, min_stock, max_stock,
               description, created_at, updated_at
        FROM products WHERE id = ?
    `, id).Scan(
		&product.ID, &product.Name, &product.Model, &product.Brand,
		&product.CategoryID, &product.Specs, &product.Unit,
		&product.PurchasePrice, &product.RetailPrice,
		&product.MinStock, &product.MaxStock, &product.Description,
		&product.CreatedAt, &product.UpdatedAt)

	if err != nil {
		if err == sql.ErrNoRows {
			return nil, fmt.Errorf("产品不存在")
		}
		return nil, fmt.Errorf("查询产品失败: %v", err)
	}

	return &product, nil
}

func UpdateProduct(db *sql.DB, product *models.Product) error {
	_, err := db.Exec(`
        UPDATE products SET
            name = ?,
            model = ?,
            brand = ?,
            category_id = ?,
            specs = ?,
            unit = ?,
            purchase_price = ?,
            retail_price = ?,
            min_stock = ?,
            max_stock = ?,
            description = ?,
            updated_at = CURRENT_TIMESTAMP
        WHERE id = ?
    `,
		product.Name, product.Model, product.Brand, product.CategoryID,
		product.Specs, product.Unit, product.PurchasePrice, product.RetailPrice,
		product.MinStock, product.MaxStock, product.Description, product.ID)

	if err != nil {
		return fmt.Errorf("更新产品失败: %v", err)
	}

	return nil
}
func DeleteProduct(db *sql.DB, id int) error {
	// 检查是否有库存记录
	var stockCount int
	err := db.QueryRow(`
        SELECT COUNT(*) FROM inventory WHERE product_id = ?
    `, id).Scan(&stockCount)

	if err != nil {
		return fmt.Errorf("检查库存记录失败: %v", err)
	}

	if stockCount > 0 {
		return fmt.Errorf("该产品存在库存记录，不能删除")
	}

	// 执行删除
	_, err = db.Exec("DELETE FROM products WHERE id = ?", id)
	if err != nil {
		return fmt.Errorf("删除产品失败: %v", err)
	}

	return nil
}

func GetProductsByCategory(db *sql.DB, categoryID int) ([]models.Product, error) {
	rows, err := db.Query(`
        SELECT id, name, model, brand, retail_price
        FROM products
        WHERE category_id = ?
        ORDER BY name ASC
    `, categoryID)

	if err != nil {
		return nil, fmt.Errorf("按分类查询产品失败: %v", err)
	}
	defer rows.Close()

	var products []models.Product
	for rows.Next() {
		var p models.Product
		if err := rows.Scan(
			&p.ID, &p.Name, &p.Model, &p.Brand, &p.RetailPrice); err != nil {
			return nil, fmt.Errorf("扫描产品数据失败: %v", err)
		}
		products = append(products, p)
	}

	return products, nil
}

func SearchProducts(db *sql.DB, keyword string) ([]models.Product, error) {
	rows, err := db.Query(`
        SELECT id, name, model, brand, retail_price
        FROM products
        WHERE name LIKE ? OR model LIKE ? OR brand LIKE ?
        ORDER BY name ASC
    `, "%"+keyword+"%", "%"+keyword+"%", "%"+keyword+"%")

	if err != nil {
		return nil, fmt.Errorf("搜索产品失败: %v", err)
	}
	defer rows.Close()

	var products []models.Product
	for rows.Next() {
		var p models.Product
		if err := rows.Scan(
			&p.ID, &p.Name, &p.Model, &p.Brand, &p.RetailPrice); err != nil {
			return nil, fmt.Errorf("扫描产品数据失败: %v", err)
		}
		products = append(products, p)
	}

	return products, nil
}
